# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function: 数据初始化对比：基金对比 全称、信息状态、属性
"""

def searchCvs1FundList():
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM datacenter_online.dc_fund a WHERE a.`status`='1' ORDER BY a.id DESC"
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50

    # for index in range(pageCount):
    for index in range(393,400):
    #本地跑0到20000，服务器跑20000到49910
        limitStart = index * 50

        print("limitStart====================================================",limitStart)

        sql = "SELECT a.id AS fundId,a.cn_name as fundName,a.info_status, a.show_permission, a.ext1 AS beizhu " \
              "FROM datacenter_online.dc_fund a " \
              "WHERE a.`status`='1' " \
              "ORDER BY a.cn_name DESC LIMIT %d,50 "%limitStart

        cursor.execute(sql)
        fundResults1 = cursor.fetchall()
        fields = cursor.description
        compareCvs2FundList(fundResults1, fields)

    db.close()

def compareCvs2FundList(fundResults1, fields):
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "cms_plus", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    for index in range(len(fundResults1)):
        fundId1 = fundResults1[index].get(u"fundId")
        #信息状态: 1-待编辑、2-待完善、3-已编辑、4-已发布、5-取消发布
        info_status1 = fundResults1[index].get(u"info_status")
        #显示权限 1:公开;2:半公开;3:未公开
        show_permission1 = fundResults1[index].get(u"show_permission")
        fundName1 = fundResults1[index].get(u"fundName")

        cmsPlusSql = "SELECT a.company_id AS companyId2, a.ext2 AS fundId1,b.full_name " \
                     "FROM tbl_com_fund a,tbl_company b " \
                     "WHERE a.company_id = b.id AND a.ext2 ='%s' AND a.`status`='1' "%fundId1

        cursor.execute(cmsPlusSql)
        cmsPlusResults2 = cursor.fetchall()
        if len(cmsPlusResults2) >0:
            for cmsPlusResults2Index in range(len(cmsPlusResults2)):
                cmsPlusCompanyId = cmsPlusResults2[cmsPlusResults2Index].get(u"companyId2")
                cmsPlusFullName = cmsPlusResults2[cmsPlusResults2Index].get(u"full_name")

                #对比1.0和2.0中基金发布状态、属性、全称
                compareCvs1and2Status(fields, cmsPlusCompanyId, info_status1, show_permission1, fundResults1[index], fundName1)

    db.close()

#1.0中info_status 1、2、3、5对应 2.0info_status的下线;4对应2.0的正常发布;1.0的未公开对应2.0的保密;半公开 对应正常发布 ；未公开对应保密发布；更新质检在1.0中没有对应
def compareCvs1and2Status(fields, companyId2, info_status1, show_permission1, fundResults1, fundName1):
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "cms_plus_20180724", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    cmsPlus0724sql = "SELECT a.full_name,a.info_status,a.fund_attr FROM tbl_company a WHERE a.`status`='1' AND a.id = '%s'" % companyId2

    cursor.execute(cmsPlus0724sql)
    cmsPlus0724sql = cursor.fetchall()
    for index in range(len(cmsPlus0724sql)):
        fund_attr2 = cmsPlus0724sql[index].get(u"fund_attr")
        info_status2 = cmsPlus0724sql[index].get(u"info_status")
        full_name2 = cmsPlus0724sql[index].get(u"full_name")
        if fundName1 != None and fundName1 != "":
            if "（管理）" in fundName1:
                fundName1Str = fundName1.split("（管理）")
                fundName1 = fundName1Str[0]
            if full_name2 == None or info_status2 =="":
                beizhuStr1=u'cvs2中缺失此条基金信息，此为cvs1中数据'
                writeDataOne(fundResults1, fields, beizhuStr1)
            else:
                if full_name2 != fundName1:
                    beizhuStr1=u'cvs2中此基金全称与cvs1中不匹配，此为cvs1中全称：' + fundName1 + "||此为cvs2中全称：" + full_name2
                    writeDataOne(fundResults1, fields, beizhuStr1)
        if info_status1 != None and info_status1 !="":
            if info_status2 == None or info_status2 =="":
                 beizhuStr1=u'cvs2中此基金信息状态与cvs1中不匹配，此为cvs1中状态：' + str(info_status1)+"||此为cvs2中状态：None"
                 writeDataOne(fundResults1, fields, beizhuStr1)
            else:
                if info_status2 == 0000:
                    isInfoStatus = (info_status1 == 1 or info_status1 == 2 or info_status1 == 3 or info_status1 == 5)
                    if isInfoStatus != True:
                        beizhuStr1=u'cvs2中此基金信息状态与cvs1中不匹配，此为cvs1中状态：' + str(info_status1)+"||此为cvs2中状态：下线"
                        writeDataOne(fundResults1, fields, beizhuStr1)
                if info_status2 == 1111:
                    if info_status1 != 4 or show_permission1 != 2:
                        beizhuStr1=u'cvs2中此基金信息状态与cvs1中不匹配，此为cvs1中状态：' + str(info_status1)+"||此为cvs2中状态：正常发布"
                        writeDataOne(fundResults1, fields, beizhuStr1)
                if info_status2 == 0100:
                    if show_permission1 != 3:
                        beizhuStr1=u'cvs2中此基金信息状态与cvs1中不匹配，此为cvs1中状态：' + str(show_permission1)+"||此为cvs2中状态：保密发布"
                        writeDataOne(fundResults1, fields, beizhuStr1)
        if fund_attr2 != None and fund_attr2 !="":
            if fund_attr2 != 1:
                    beizhuStr1=u'此条数据在cvs2中属性不是基金，fund_attr为：' +str(fund_attr2)
                    writeDataOne(fundResults1, fields, beizhuStr1)

    db.close()

def writeDataOne(orgResults1, fields, beizhuStr1):
     orgResults1.update(beizhu=beizhuStr1)
     writeExcel(orgResults1, fields)


def writeExcel(results,fields):

    fileName = 'dataViewFundDetail.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
